package com.store.framework.db;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.store.framework.dao.IAppPerDao;
import com.store.framework.entry.AppPerTbl;

public class AppPerDao implements IAppPerDao {
	private static Log LOG = LogFactory.getLog(AppPerDao.class);

	public int add(AppPerTbl d ){
	   int a=0;
		String sql = "insert into app_per_tbl (Id,PerName,PenName,PerSex,Perbirthday,"
				+ "PerResaddres,PerHomeTaddres,PerConstellation,Completeness,SchoolId,WorksId,PerImgId,"
				+ "AddDate,DelPer,DelDate,EditPer,EditDate,PerState,Del,Remarks) values (?,?,?,?,?,?,?,?,?,"
				+ "?,?,?,?,?,?,?,?,?,?,?)";
		try {
			DaoUtil.executeUpdate(sql, d.getId(),d.getPerName(),d.getPenName(),
					d.getPerSex(),d.getPerbirthday(),
					d.getPerResaddres(),d.getPerHomeTaddres(),d.getPerConstellation(),
					d.getCompleteness(),d.getSchoolId(),d.getWorksId(),d.getPerImgId(),
					d.getAddDate(),d.getDelPer(),d.getDelDate(),d.getEditPer(),d.getEditDate(),
					d.getPerState(),d.getDel(),d.getRemarks());
			a=1;
		} catch (SQLException e) {
			a=0;
			LOG.error(e.getMessage(),e);
			throw new RuntimeException(e.getMessage(),e);
		}
		return a;

	}
//	private final static SimpleDateFormat DATE_YYYYMMDDHHMMSS= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//	private final static SimpleDateFormat DATE_YYYYMMDD= new SimpleDateFormat("yyyy-MM-dd");
	@Override
	public List<AppPerTbl> select() {
		// TODO Auto-generated method stub
		String sql ="select *from app_per_tbl";
		List<AppPerTbl> list= new ArrayList<AppPerTbl>();
		try {
			CachedRowSet cs = DaoUtil.SimpleQuery(sql);
			while(cs.next()){
				AppPerTbl appPerTbl = new AppPerTbl();
				appPerTbl.setId(cs.getString("Id"));
				appPerTbl.setPerName(cs.getString("PerName"));
				appPerTbl.setPenName(cs.getString("PenName"));
				appPerTbl.setPassword(cs.getString("Password"));
				appPerTbl.setPerAge(cs.getInt("PerAge"));
				appPerTbl.setPerSex(cs.getString("PerSex"));
				appPerTbl.setPerbirthday(cs.getDate("Perbirthday")==null?null:new SimpleDateFormat("yyyy-MM-dd").format(cs.getDate("Perbirthday")));
				appPerTbl.setPerResaddres(cs.getString("perResaddres"));
				appPerTbl.setPerHomeTaddres(cs.getString("perHomeTaddres"));
				appPerTbl.setPerConstellation(cs.getString("perConstellation"));
				appPerTbl.setCompleteness(cs.getDouble("completeness"));
				appPerTbl.setSchoolId(cs.getString("schoolId"));
				appPerTbl.setWorksId(cs.getString("worksId"));
				appPerTbl.setPerImgId(cs.getString("perImgId"));
				appPerTbl.setAddDate(cs.getDate("addDate")==null?null:cs.getDate("addDate").getTime());
				appPerTbl.setDelPer(cs.getString("delPer"));
				appPerTbl.setDelDate(cs.getDate("delDate")==null?null:cs.getDate("delDate").getTime());
				appPerTbl.setEditPer(cs.getString("editPer"));
				appPerTbl.setEditDate(cs.getDate("editDate")==null?null:cs.getDate("editDate").getTime());
				appPerTbl.setDel(cs.getLong("del"));
				appPerTbl.setRemarks(cs.getString("remarks"));
				list.add(appPerTbl);
			}	
		} catch (SQLException e) {
			list=null;
			LOG.error(e.getMessage(),e);
			throw new RuntimeException(e.getMessage(),e);
		}
		return list;
	}
	public int getCount(){
		String sql ="select count(0) c from app_per_tbl";
		int count = 0;
		try {
			CachedRowSet cs = DaoUtil.SimpleQuery(sql);
			if(cs.next()){
				count = cs.getInt(1);
			}
		} catch (SQLException e) {
			count = 0; 
			LOG.error(e);
		}
		return count;
	}
	public List<AppPerTbl> select(Long start , Long limit){
		// TODO Auto-generated method stub
		String sql ="select * from app_per_tbl limit "+start +"," +limit;
		List<AppPerTbl> list= new ArrayList<AppPerTbl>();
		try {
			CachedRowSet cs = DaoUtil.SimpleQuery(sql);
			while(cs.next()){
				AppPerTbl appPerTbl = new AppPerTbl();
				appPerTbl.setId(cs.getString("Id"));
				appPerTbl.setPerName(cs.getString("PerName"));
				appPerTbl.setPenName(cs.getString("PenName"));
				appPerTbl.setPerAge(cs.getInt("PerAge"));
				appPerTbl.setPerSex(cs.getString("PerSex"));
				appPerTbl.setPerbirthday(cs.getDate("Perbirthday")==null?null:new SimpleDateFormat("yyyy-MM-dd").format(cs.getDate("Perbirthday")));
				appPerTbl.setPerResaddres(cs.getString("perResaddres"));
				appPerTbl.setPerHomeTaddres(cs.getString("perHomeTaddres"));
				appPerTbl.setPerConstellation(cs.getString("perConstellation"));
				appPerTbl.setCompleteness(cs.getDouble("completeness"));
				appPerTbl.setSchoolId(cs.getString("schoolId"));
				appPerTbl.setWorksId(cs.getString("worksId"));
				appPerTbl.setPerImgId(cs.getString("perImgId"));
				appPerTbl.setAddDate(cs.getDate("addDate")==null?null:cs.getDate("addDate").getTime());
				appPerTbl.setDelPer(cs.getString("delPer"));
				appPerTbl.setDelDate(cs.getDate("delDate")==null?null:cs.getDate("delDate").getTime());
				appPerTbl.setEditPer(cs.getString("editPer"));
				appPerTbl.setEditDate(cs.getDate("editDate")==null?null:cs.getDate("editDate").getTime());
				appPerTbl.setDel(cs.getLong("del"));
				appPerTbl.setRemarks(cs.getString("remarks"));
				list.add(appPerTbl);
			}	
		} catch (SQLException e) {
			list=null;
			LOG.error(e.getMessage(),e);
			throw new RuntimeException(e.getMessage(),e);
		}
		return list;
	}
	
	
	@Override
	public int delete(AppPerTbl app) {
		int a=0;
		String sql = "delete from app_per_tbl where Id = ?";
		try {
			DaoUtil.executeUpdate(sql, app.getId());
			a=1;
		} catch (SQLException e) {
			a=0;
			LOG.error(e.getMessage(),e);
			throw new RuntimeException(e.getMessage(),e);
		}
		return a;
	}
	
	@Override
	public int update(AppPerTbl app){
		int a=0;
		String sql = "update  app_per_tbl set penName=?,perName=?,perAge=?,perSex=?,perbirthday=? where Id = ?";
		try {
			/*
			 * {"penName":"444",
			 * "perName":"12sss",
			 * "perAge":"6",
			 * "perSex":"男",
			 * "perbirthday":"2014-05-12",
			 * "id":"4"}
			*/
			DaoUtil.executeUpdate(sql, 
					app.getPenName(),
					app.getPerName(),
					app.getPerAge(),
					app.getPerSex(),
					app.getPerbirthday(),
					app.getId());
			a=1;
		} catch (SQLException e) {
			a=0;
			LOG.error(e.getMessage(),e);
			throw new RuntimeException(e.getMessage(),e);
		}
		return a;
	}
}
